Skip Headers

Oracle® OLAP Reference 10g Release 2 (10.2) Part Number B14350-01 |
Home | Book List | Contents | Index | Master Index | Feedback

Previous | Next
| View PDF

# Example: Creating a Cube

The following statements drop the cube `SALES_CUBE`, re-create it, and add the dimensions `TIME_DIM`, `GEOG_DIM`, and `PRODUCT_DIM`.

Dropping the cube removes the cube entity, along with its measures, from the OLAP Catalog. However, dropping the cube does not cause the cube's dimensions to be dropped.

    execute cwm2_olap_cube.drop_cube('JSMITH', 'SALES_CUBE');
    execute cwm2_olap_cube.create_cube
         ('JSMITH', 'SALES_CUBE', 'Sales', 'Sales Cube',
          'Sales dimensioned over geography, product, and time' );
    execute cwm2_olap_cube.add_dimension_to_cube
         ('JSMITH', 'SALES_CUBE', 'JSMITH', 'TIME_DIM');
    execute cwm2_olap_cube.add_dimension_to_cube
         ('JSMITH', 'SALES_CUBE', 'JSMITH', 'GEOG_DIM');
    execute cwm2_olap_cube.add_dimension_to_cube
         ('JSMITH', 'SALES_CUBE', 'JSMITH', 'PRODUCT_DIM');

# Summary of CWM2_OLAP_CUBE Subprograms

Table 9-1 CWM2_OLAP_CUBE Subprograms

Subprogram | Description
--- | ---
ADD_DIMENSION_TO_CUBE Procedure | Adds a dimension to a cube.
CREATE_CUBE Procedure | Creates a cube.
DROP_CUBE Procedure | Drops a cube.
LOCK_CUBE Procedure | Locks a cube's metadata for update.
REMOVE_DIMENSION_FROM_CUBE Procedure | Removes a dimension from a cube.
SET_AGGREGATION_OPERATOR Procedure | Sets the aggregation operators for rolling up the cube's data.
SET_CUBE_NAME Procedure | Sets the name of a cube.
SET_DEFAULT_CUBE_DIM_CALC_HIER Procedure | Sets the default calculation hierarchy for a dimension of the cube.
SET_DESCRIPTION Procedure | Sets the description for a cube.
SET_DISPLAY_NAME Procedure | Sets the display name for a cube.
SET_MV_SUMMARY_CODE Procedure | Sets the format for materialized views associated with a cube.
SET_SHORT_DESCRIPTION Procedure | Sets the short description for a cube.

## ADD_DIMENSION_TO_CUBE Procedure

This procedure adds a dimension to a cube.

Syntax

    ADD_DIMENSION_TO_CUBE  (
              cube_owner         IN   VARCHAR2,
              cube_name          IN   VARCHAR2,
              dimension_owner    IN   VARCHAR2,
              dimension_name     IN   VARCHAR2);

Parameters

Table 9-2 ADD_DIMENSION_TO_CUBE Procedure Parameters

Parameter | Description
--- | ---
`cube_owner` | Owner of the cube.
`cube_name` | Name of the cube.
`dimension_owner` | Owner of the dimension to be added to the cube.
`dimension_name` | Name of the dimension to be added to the cube.

## CREATE_CUBE Procedure

This procedure creates a new cube in the OLAP Catalog.

Descriptions and display properties must also be established as part of cube creation. Once the cube has been created, you can override these properties by calling other procedures in this package.

Syntax

    CREATE_CUBE (
              cube_owner            IN   VARCHAR2,
              cube_name             IN   VARCHAR2,
              display_name          IN   VARCHAR2,
              short_description     IN   VARCHAR2,
              description           IN   VARCHAR2);

Parameters

Table 9-3 CREATE_CUBE Procedure Parameters

Parameter | Description
--- | ---
`cube_owner` | Owner of the cube.
`cube_name` | Name of the cube.
`display_name` | Display name for the cube.
`short_description` | Short description of the cube.
`description` | Description of the cube.

## DROP_CUBE Procedure

This procedure drops a cube from the OLAP Catalog.

Note: When a cube is dropped, its associated measures are also dropped. However, the cube's dimensions are not dropped. They might be mapped within the context of a different cube.

Syntax

    DROP_CUBE (
             cube_owner     IN   VARCHAR2,
             cube_name      IN   VARCHAR2);

Parameters

Table 9-4 DROP_CUBE Procedure Parameters

Parameter | Description
--- | ---
`cube_owner` | Owner of the cube.
`cube_name` | Name of the cube.

## LOCK_CUBE Procedure

This procedure locks the cube's metadata for update by acquiring a database lock on the row that identifies the cube in the `CWM2` model table.

Syntax

    LOCK_CUBE (
               cube_owner        IN   VARCHAR2,
               cube_name         IN   VARCHAR2.
               wait_for_lock     IN   BOOLEAN DEFAULT FALSE);

Parameters

Table 9-5 LOCK_CUBE Procedure Parameters

Parameter | Description
--- | ---
`cube_owner` | Owner of the cube.
`cube_name` | Name of the cube.
`wait_for_lock` | (Optional) Whether or not to wait for the cube to be available when it is already locked by another user. If you do not specify a value for this parameter, the procedure does not wait to acquire the lock.

## REMOVE_DIMENSION_FROM_CUBE Procedure

This procedure removes a dimension from a cube.

Syntax

    REMOVE_DIMENSION_FROM_CUBE  (
              cube_owner         IN   VARCHAR2,
              cube_name          IN   VARCHAR2,
              dimension_owner    IN   VARCHAR2,
              dimension_name     IN   VARCHAR2);

Parameters

Table 9-6 REMOVE_DIMENSION_FROM_CUBE Procedure Parameters

Parameter | Description
--- | ---
`cube_owner` | Owner of the cube.
`cube_name` | Name of the cube.
`dimension_owner` | Owner of the dimension to be removed from the cube.
`dimension_name` | Name of the dimension to be removed from the cube.

## SET_AGGREGATION_OPERATOR Procedure

This procedure sets the aggregation operator for rolling up a cube's data over its dimensions. The cube must be mapped to a star schema, with a storage type indicator of '`LOWESTLEVEL`'. (See "Joining Fact Tables with Dimension Tables".)

The aggregation operators supported by the OLAP Catalog are listed in Table 1-10, "Aggregation Operators".

When no aggregation operator is specified, the operator is addition. The view `ALL_OLAP2_AGGREGATION_USES` lists the nondefault aggregation operators that have been specified for cubes. See "ALL_OLAP2_AGGREGATION_USES".

Syntax

    SET_AGGREGATION_OPERATOR (
              cube_owner        IN   VARCHAR2,
              cube_name         IN   VARCHAR2,
              aggop_spec        IN   VARCHAR2);

Parameters

Table 9-7 SET_AGGREGATION_OPERATOR Procedure Parameters

Parameter | Description
--- | ---
`cube_owner` | Owner of the cube.
`cube_name` | Name of the cube.
`aggop_spec` | A string that specifies the aggregation operators for the cube. Each aggregation operator that you specify applies to all of the cube's measures over a given hierarchy of a given dimension of the cube. If you do not specify a hierarchy, the operator applies to all hierarchies of the dimension. By default, the aggregation operator is addition. Enclose the string in single quotes, and separate each dimension/operator clause with a semicolon as follows: '`DIM:``dim1_owner.dim1_name``/AGGOP:``operator`;`DIM:``dim2_owner.dim2_name``/AGGOP:``operator`;...........' If the operator should apply to a specific hierarchy of a dimension, use the optional '`HIER`' clause after the `DIM` clause: `/HIER:``hiername1` For weighted operators, the '`AGGOP`' clause may optionally be followed with a `WEIGHTBY` clause: `/WEIGHTBY:``TblOwner.TblName.ColName;` NOTE: The cube's data will be aggregated in the order of the dimension clauses in the aggregation specification.

Example

The following example specifies that data in the `ANALYTIC_CUBE` should be aggregated using addition over the Standard hierarchies of the Product and Channel dimensions, using the `MAX` operator over the Standard hierarchy of Geography, and using `AVERAGE` over the Year to Date hierarchy of the Time dimension. Any unspecified hierarchies will use addition.

    execute cwm2_olap_cube.set_aggregation_operator
                ('XADEMO', 'ANALYTIC_CUBE',
                 'DIM:XADEMO.PRODUCT/HIER:STANDARD/AGGOP:SUM;
                  DIM:XADEMO.GEOGRAPHY/HIER:STANDARD/AGGOP:MAX;
                  DIM:XADEMO.TIME/HIER:YTD/AGGOP:AVERAGE;
                  DIM:XADEMO.CHANNEL/HIER:STANDARD/AGGOP:SUM;');

The following example shows the same specification including a weighted operator for Product.

    execute cwm2_olap_cube.set_aggregation_operator
                ('XADEMO', 'ANALYTIC_CUBE',
                 'DIM:XADEMO.PRODUCT/HIER:STANDARD/AGGOP:SUM/
                                    WEIGHTBY:XADEMO.XADEMO_SALES_VIEW.COSTS;
                  DIM:XADEMO.GEOGRAPHY/HIER:STANDARD/AGGOP:MAX;
                  DIM:XADEMO.TIME/HIER:YTD/AGGOP:AVERAGE;
                  DIM:XADEMO.CHANNEL/HIER:STANDARD/AGGOP:SUM;');

In the following example, aggregation operators are specified for all hierarchies of each dimension.

    execute cwm2_olap_cube.set_aggregation_operator
                ('XADEMO', 'ANALYTIC_CUBE',
                  DIM:XADEMO.PRODUCT/AGGOP:SUM;
                  DIM:XADEMO.GEOGRAPHY/AGGOP:MAX;
                  DIM:XADEMO.TIME/AGGOP:AVERAGE;
                  DIM:XADEMO.CHANNEL/AGGOP:SUM;');

See Also

"Aggregating the Cube's Data in the Analytic Workspace"

## SET_CUBE_NAME Procedure

This procedure sets the name for a cube.

Syntax

    SET_CUBE_NAME (
              cube_owner        IN   VARCHAR2,
              cube_name         IN   VARCHAR2,
              set_cube_name     IN   VARCHAR2);

Parameters

Table 9-8 SET_CUBE_NAME Procedure Parameters

Parameter | Description
--- | ---
`cube_owner` | Owner of the cube.
`cube_name` | Original name of the cube.
`set_cube_name` | New name for the cube.

## SET_DEFAULT_CUBE_DIM_CALC_HIER Procedure

This procedure sets the default calculation hierarchy for a dimension of this cube.

Syntax

    SET_DEFAULT_CUBE_DIM_CALC_HIER (
              cube_owner       IN   VARCHAR2,
              cube_name        IN   VARCHAR2,
              dimension_owner  IN   VARCHAR2,
              dimension_name   IN   VARCHAR2,
              hierarchy_name   IN   VARCHAR2);

Parameters

Table 9-9 SET_DEFAULT_CUBE_DIM_CALC_HIER Procedure Parameters

Parameter | Description
--- | ---
`cube_owner` | Owner of the cube.
`cube_owner` | Name of the cube.
`dimension_owner` | Owner of the dimension.
`dimension_name` | Name of the dimension.
`hierarchy_name` | Name of the hierarchy to be used by default for this dimension.

## SET_DESCRIPTION Procedure

This procedure sets the description for a cube.

Syntax

    SET_DESCRIPTION (
              cube_owner     IN   VARCHAR2,
              cube_name      IN   VARCHAR2,
              description    IN   VARCHAR2);

Parameters

Table 9-10 SET_DESCRIPTION Procedure Parameters

Parameter | Description
--- | ---
`cube_owner` | Owner of the cube.
`cube_name` | Name of the cube.
`description` | Description of the cube.

## SET_DISPLAY_NAME Procedure

This procedure sets the display name for a cube.

Syntax

    SET_DISPLAY_NAME (
              cube_owner     IN   VARCHAR2,
              cube_name      IN   VARCHAR2,
              display_name   IN   VARCHAR2);

Parameters

Table 9-11 SET_DISPLAY_NAME Procedure Parameters

Parameter | Description
--- | ---
`cube_owner` | Owner of the cube.
`cube_name` | Name of the cube.
`display_name` | Display name for the cube.

## SET_MV_SUMMARY_CODE Procedure

This procedure specifies the form of materialized views for this cube. Materialized views may be in Grouping Set (`groupingset`) or Rolled Up (`rollup`) form.

In a materialized view in Rolled Up form, all the dimension key columns are populated, and data may only be accessed when its full lineage is specified.

In a materialized view in Grouping Set form, dimension key columns may contain null values, and data may be accessed simply by specifying one or more levels.

Syntax

    SET_MV_SUMMARY_CODE (
              cube_owner            IN   VARCHAR2,
              cube_name             IN   VARCHAR2,
              summary_code          IN   VARCHAR2);

Parameters

Table 9-12 SET_MV_SUMMARY_CODE Procedure Parameters

Parameter | Description
--- | ---
`cube_owner` | Owner of the cube.
`cube_name` | Name of the cube.
`summary_code` | One of the following case-insensitive values: `rollup`, for Rolled Up form. `groupingset`, for Grouping Set form.

## SET_SHORT_DESCRIPTION Procedure

This procedure sets the short description for a cube.

Syntax

    SET_SHORT_DESCRIPTION (
              cube_owner            IN   VARCHAR2,
              cube_name             IN   VARCHAR2,
              short_description     IN   VARCHAR2);

Parameters

Table 9-13 SET_SHORT_DESCRIPTION Procedure Parameters

Parameter | Description
--- | ---
`cube_owner` | Owner of the cube.
`cube_name` | Name of the cube.
`short_description` | Short description of the cube.